package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Squad;
import edu.tms.bean.Student;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 小组信息dao
 * @author Administrator
 *
 */
public class SquadDaoImp  implements CommnonDao<Squad>{
	/**
	 * 保存内容
	 * 返回该条记录的自增长值（id）
	 * @param value
	 * @throws Exception
	 */
	@Override
	public long save(Squad value) throws Exception {
		String sql="insert into t_squad value(null,?,?,?,?,?);";
		Squad squad=(Squad)value;
		long num=JdbcUtil.insertAuto(sql,
				squad.getSquadName(),
				squad.getClassName(),
				squad.getSquadLeader(),
				squad.getSquadSize(),
				squad.getSquadState());
		return num;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Squad value) throws Exception {
		String sql="update t_squad set squad_name=?,class_name=?,"
				+ "squad_leader=?,squad_size=?,squad_state=? where squad_id=?;";
		Squad squad=(Squad)value;
		int num=JdbcUtil.update(sql, 
				squad.getSquadName(),
				squad.getClassName(),
				squad.getSquadLeader(),
				squad.getSquadSize(),
				squad.getSquadState(),
				squad.getSquadId());
		return num;
	}
	/**
	 * 删除内容
	 * 相关联的表：提交人？
	 * 学生表t_student
	 * 管理员表t_admin，
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Squad value) throws Exception {
		String sql="delete * from t_squad where squad_id=?;"
				+ "update t_student set stu_squad_id=null where stu_squad_id=?;";
		return JdbcUtil.update(sql, value,value);
	}
	
	public int delete(Integer value) throws Exception {
		String sql1="delete from t_squad where squad_id=?;";
		JdbcUtil.update(sql1, value);
		String sql2="update t_student set stu_squad_id=0 where stu_squad_id=?;";
		int num=JdbcUtil.update(sql2, value);
		return num;
	}
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Squad> findAll(int pageNo, int pageSize) throws Exception {
		String sql="SELECT sq.squad_name , sq.class_name , st.stu_name , sq.squad_size , sq.squad_state "
					+"FROM t_squad AS sq , t_student AS st "
					+"WHERE st.stu_id = sq.squad_leader limit ?,?";
		List<Squad> squad=JdbcUtil.query(Squad.class, sql, (pageNo-1)*pageSize,pageSize);
		return squad;
	}
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Squad> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT st.stu_name,st.stu_sex,st.stu_phone,st.stu_qq,st.stu_email,sq.squad_name "
					+"FROM t_squad AS sq , t_student AS st "
					+"WHERE sq.squad_id = stu_squad_id AND sq.squad_id = ? limit ?,?;";
		List<Squad> squad=JdbcUtil.query(Squad.class, sql, key,(pageNo-1)*pageSize,pageSize);
		return squad;
	}
	
	@SuppressWarnings("unchecked")
	public List<Student> findStuName(Object key) throws Exception {
		String sql="SELECT * FROM t_student,t_squad WHERE t_student.stu_squad_id=t_squad.squad_id AND "
					+" t_student.`stu_id`=?;";
		List<Student> squad=JdbcUtil.query(Student.class, sql, key);
		return squad;
	}
	
	@SuppressWarnings("unchecked")
	public List<Student> findOtherStuName(Object key) throws Exception {
		String sql="SELECT * FROM t_student,t_squad WHERE t_student.stu_squad_id=t_squad.squad_id AND "
					+" t_student.`stu_id`!=?;";
		List<Student> squad=JdbcUtil.query(Student.class, sql, key);
		return squad;
	}
	
	@Override
	public Squad findByKey(Object key) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Squad findBySquadId(long key) throws Exception {
		String sql = "select * from t_squad where squad_id = ?";
		return (Squad) JdbcUtil.uniqueBean(Squad.class, sql, key);
	}

	@SuppressWarnings("unchecked")
	public List<Squad> findSquadByStuName(Object key) throws Exception {
		String sql="SELECT * FROM t_student,t_squad WHERE t_student.stu_squad_id=t_squad.squad_id AND t_student.`stu_name`=? or t_squad.squad_name=?";
		List<Squad> squad = JdbcUtil.query(Squad.class, sql, key,key);
		return squad;
	}
	
	@SuppressWarnings("unchecked")
	public List<Squad> findSquadBySquadName(Object key) throws Exception {
		String sql="SELECT * FROM t_student,t_squad WHERE t_student.stu_squad_id=t_squad.squad_id AND t_squad.squad_name=?";
		List<Squad> squad=JdbcUtil.query(Squad.class, sql, key);
		return squad;
	}
	//返回组长信息
	@SuppressWarnings("unchecked")
	public List<Squad> xiaozu(int id) throws Exception{
		String sql="SELECT squad_name,squad_id,stu_name,stu_phone ";
			   sql+="FROM t_student,t_squad WHERE t_squad.squad_leader=";
			   	sql+="t_student.stu_id AND squad_id=?;";
	 List<Squad> squad=JdbcUtil.query(Squad.class, sql,id);
	 return squad;
	}
	//返回组员名单
	@SuppressWarnings("unchecked")
	public List<Student>  zuyuan(int id) throws Exception{
		 String sql="SELECT stu_name FROM t_student,t_squad WHERE ";
		 		sql+="t_squad.squad_id=t_student.stu_squad_id AND ";
		 	   sql+="t_squad.squad_id!=t_student.stu_id AND squad_id=?;";
		 List<Student>  st=JdbcUtil.query(Squad.class, sql,id);
		 return st;
	}
	//计算总页数
	@SuppressWarnings("unchecked")
	public int  tatolCount(int pageSize) throws Exception{
		String sql="select * from t_squad";
		List<Squad> sq=JdbcUtil.query(Squad.class, sql);
		int page=sq.size();
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	//根据id查看该小组信息
	@SuppressWarnings("unchecked")
	public List<Squad> findById(int sid) throws Exception{
		String sql="select * from t_squad where squad_id=?";
		List<Squad> squad=JdbcUtil.query(Squad.class, sql, sid);
		return squad;
	}
}